package es.mgil.whoWantsToBeAMillonaire.util;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * Helper class to manage the local database of the application
 * 
 * @author marc
 */
public class MillonaireDatabaseHelper extends SQLiteOpenHelper {
	private static final String dbName = "millonaire";
	private final String localScoresTable = "localScores";

	public MillonaireDatabaseHelper(Context context) {
		super(context, dbName, null, 1);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// create the database if not exists
		String createTable = "CREATE TABLE " + localScoresTable + " ("
				+ "id INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ "name TEXT NOT NULL, " + "longitude NUMERIC, "
				+ "latitude NUMERIC, " + "address TEXT, "
				+ "score INTEGER NOT NULL)";
		db.execSQL(createTable);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

	}

	/**
	 * Delete all local scores
	 */
	public void deleteLocalScores() {
		getWritableDatabase().delete(localScoresTable, null, null);
	}

	/**
	 * Inserts the given score for the current user of the game and the current
	 * location saved in the preferences. Only inserts it if the user have not
	 * the same score already registered
	 */
	public void insertLocalScore(String name, float longitude, float latitude,
			String address, int score) {
		Cursor cursor = getReadableDatabase().query(localScoresTable, null,
				"name = '" + name + "' and score = " + score, null, null, null,
				null);
		if (cursor.getCount() > 0) {
			return;
		}

		ContentValues cv = new ContentValues();
		cv.put("name", name);
		cv.put("longitude", longitude);
		cv.put("latitude", latitude);
		cv.put("address", address);
		cv.put("score", score);
		getWritableDatabase().insert(localScoresTable, null, cv);
	}

	/**
	 * Allow to check if the given score is the maximum local score.
	 */
	public boolean isMaximumLocal(String name, int score) {
		Cursor cursor = getReadableDatabase().query(localScoresTable, null,
				"name = '" + name + "' and score >= " + score, null, null,
				null, null);
		if (cursor.getCount() == 1) {
			return true;
		} else {
			return false;
		}
	}

	/**
	 * Return all the local scores for all the users, ordered by the score
	 */
	public List<ScoreRow> getLocalScores() {
		List<ScoreRow> list = new ArrayList<ScoreRow>();
		String[] columns = { "name", "address", "score" };

		Cursor cursor = getReadableDatabase().query(localScoresTable, columns,
				null, null, null, null, "score");
		int nameId = cursor.getColumnIndex("name");
		int addressId = cursor.getColumnIndex("address");
		int scoreId = cursor.getColumnIndex("score");
		if (cursor.getCount() == 0) {
			return list;
		}

		// doing the following we will return a reverse ordered list, having
		// first the highest scores
		cursor.moveToLast();
		do {
			String name = null;
			String address = null;
			int score = -1;
			try {
				name = cursor.getString(nameId);
				address = cursor.getString(addressId);
				score = cursor.getInt(scoreId);
			} catch (Exception e) {
				if (name == null) {
					name = "";
				}
				if (address == null) {
					address = "";
				}
				if (score == -1) {
					score = 0;
				}
			}
			ScoreRow sr = new ScoreRow(name, address, score);
			list.add(sr);
		} while (cursor.moveToPrevious());

		return list;
	}
}
